import pymysql


def creat_table():
    db = pymysql.connect(host='49.233.39.160', user='user', database='lebo16', port=3306,
                         password='leboAa!#$123', charset='utf8')
    cur = db.cursor()
    cur.execute('DROP TABLE IF EXISTS students_zbddhz')
    sql = """ create table students_zbddhz(
    studentNo int auto_increment primary key,
  name varchar(10),
  sex varchar(1),
  hometown varchar(20),
  age tinyint(4),
  class varchar(10),
  card varchar(20)
  );
"""
    try:
        cur.execute(sql)
        print('数据库创建成功')
    except Exception as e:
        print('数据库创建失败的原因：%s' % e)
    finally:
        cur.close()
        db.close()


def insert_table():
    db = pymysql.connect(host='49.233.39.160', user='user', database='lebo16', port=3306,
                         password='leboAa!#$123', charset='utf8')
    cursor = db.cursor()

    sql = """insert into students_zbddhz values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')
"""
    try:
        cursor.execute(sql)
        db.commit()
        print("插入数据成功")
    except Exception as e:
        print("插入数据库失败：原因：%s" % e)
        db.rollback()
    finally:
        db.close()


creat_table()
insert_table()
